package com.pengtu.dao.detect;

import java.io.Serializable;
import java.util.List;

import org.apache.commons.lang.StringUtils;
import org.springframework.stereotype.Repository;

import com.pengtu.dao.hibernate.HibernateDao;
import com.pengtu.entity.detect.Safetyspot;
import com.pengtu.utils.DateUtils;

@Repository
public class SafetyspotDao extends HibernateDao<Safetyspot, Serializable> {
	
	public void save(Safetyspot entity) {
		 if(StringUtils.isEmpty(entity.getId())){
			 entity.setId(null);
			 entity.setCreateDate(DateUtils.getCurrentDate());
		 }
		super.save(entity);
	}

	public List<Safetyspot> select(String batch, String projectId, String startTime, String endTime) {
		String hql = "select new com.pengtu.entity.detect.Safetyspot(" +
		"sum(u.countF601),sum(u.passF601),sum(u.countF602),sum(u.passF602)," +
		"sum(u.countF603),sum(u.passF603),sum(u.countF604),sum(u.passF604)," +
		"sum(u.countF605),sum(u.passF605),sum(u.countF606),sum(u.passF606)," +
		"sum(u.countF607),sum(u.passF607),sum(u.countF608),sum(u.passF608)," +
		"sum(u.countF609),sum(u.passF609),sum(u.countF610),sum(u.passF610)) from Safetyspot u where 1 = 1";
		if(StringUtils.isNotEmpty(batch)){
			hql += " and u.spotReport.batch = '" + batch + "'";
		}
		
		if(StringUtils.isNotEmpty(projectId)){
			hql += " and u.projectId = '" + projectId +"'";
		}
		if(StringUtils.isNotEmpty(startTime)){
			hql += " and u.reportdate >= to_date('"+startTime+"','yyyy-MM-dd')";
		}
		if(StringUtils.isNotEmpty(endTime)){
			hql += " and u.reportdate < to_date('"+endTime+"','yyyy-MM-dd')";
		}
		if(StringUtils.isNotEmpty(projectId)){
			hql +=  " group by u.projectId ";
		}
		return this.find(hql);
	}
	
	public List<Safetyspot> selectFen(String socialLevel, String techGrade,String startTime, String endTime) {
		String hql = "select new com.pengtu.entity.detect.Safetyspot(" +
				"sum(u.countF601),sum(u.passF601),sum(u.countF602),sum(u.passF602)," +
				"sum(u.countF603),sum(u.passF603),sum(u.countF604),sum(u.passF604)," +
				"sum(u.countF605),sum(u.passF605),sum(u.countF606),sum(u.passF606)," +
				"sum(u.countF607),sum(u.passF607),sum(u.countF608),sum(u.passF608)," +
				"sum(u.countF609),sum(u.passF609),sum(u.countF610),sum(u.passF610)) from Safetyspot u where 1 = 1";
			
		
		if(StringUtils.isNotEmpty(socialLevel)){
			hql += " and u.spotReport.projectInfo.socialLevel = '" + socialLevel + "'";
		}
		
		if(StringUtils.isNotEmpty(techGrade)){
			hql += " and u.spotReport.projectInfo.techGrade = '" + techGrade +"'";
		}
		if(StringUtils.isNotEmpty(startTime)){
			hql += " and u.reportdate >= to_date('"+startTime+"','yyyy-MM-dd')";
		}
		if(StringUtils.isNotEmpty(endTime)){
			hql += " and u.reportdate < to_date('"+endTime+"','yyyy-MM-dd')";
		}
		
		//hql +=  " group by u.projectId ";
		
		return this.find(hql);
	}
	
	public List<Safetyspot> selectFenT(String socialLevel, String techGrade,String startTime, String endTime) {
		String hql = "select new com.pengtu.entity.detect.Safetyspot(" +
				"sum(u.countF601),sum(u.passF601),sum(u.countF602),sum(u.passF602)," +
				"sum(u.countF603),sum(u.passF603),sum(u.countF604),sum(u.passF604)," +
				"sum(u.countF605),sum(u.passF605),sum(u.countF606),sum(u.passF606)," +
				"sum(u.countF607),sum(u.passF607),sum(u.countF608),sum(u.passF608)," +
				"sum(u.countF609),sum(u.passF609),sum(u.countF610),sum(u.passF610)) from Safetyspot u where 1 = 1";
			
		
		if(StringUtils.isNotEmpty(socialLevel)){
			hql += " and u.spotReport.projectInfo.socialLevel = '" + socialLevel + "'";
		}
		
		if(StringUtils.isNotEmpty(techGrade)){
			hql += " and u.spotReport.projectInfo.techGrade not in (" + techGrade +")";
		}
		if(StringUtils.isNotEmpty(startTime)){
			hql += " and u.reportdate >= to_date('"+startTime+"','yyyy-MM-dd')";
		}
		if(StringUtils.isNotEmpty(endTime)){
			hql += " and u.reportdate < to_date('"+endTime+"','yyyy-MM-dd')";
		}
		
		//hql +=  " group by u.projectId ";
		
		return this.find(hql);
	}
	
	public List<Safetyspot> selectFenW(String socialLevel, String techGrade,String startTime, String endTime) {
		String hql = "select new com.pengtu.entity.detect.Safetyspot(" +
				"sum(u.countF601),sum(u.passF601),sum(u.countF602),sum(u.passF602)," +
				"sum(u.countF603),sum(u.passF603),sum(u.countF604),sum(u.passF604)," +
				"sum(u.countF605),sum(u.passF605),sum(u.countF606),sum(u.passF606)," +
				"sum(u.countF607),sum(u.passF607),sum(u.countF608),sum(u.passF608)," +
				"sum(u.countF609),sum(u.passF609),sum(u.countF610),sum(u.passF610)) from Safetyspot u where 1 = 1";
			
		
		if(StringUtils.isNotEmpty(socialLevel)){
			hql += " and u.spotReport.projectInfo.socialLevel not in (" + socialLevel + ")";
		}
		
		if(StringUtils.isNotEmpty(techGrade)){
			hql += " and u.spotReport.projectInfo.techGrade = '" + techGrade + "'";
		}
		if(StringUtils.isNotEmpty(startTime)){
			hql += " and u.reportdate >= to_date('"+startTime+"','yyyy-MM-dd')";
		}
		if(StringUtils.isNotEmpty(endTime)){
			hql += " and u.reportdate < to_date('"+endTime+"','yyyy-MM-dd')";
		}
		
		//hql +=  " group by u.projectId ";
		
		return this.find(hql);
	}
	
	public List<Safetyspot> selectFenF(String socialLevel, String techGrade,String startTime, String endTime) {
		String hql = "select new com.pengtu.entity.detect.Safetyspot(" +
				"sum(u.countF601),sum(u.passF601),sum(u.countF602),sum(u.passF602)," +
				"sum(u.countF603),sum(u.passF603),sum(u.countF604),sum(u.passF604)," +
				"sum(u.countF605),sum(u.passF605),sum(u.countF606),sum(u.passF606)," +
				"sum(u.countF607),sum(u.passF607),sum(u.countF608),sum(u.passF608)," +
				"sum(u.countF609),sum(u.passF609),sum(u.countF610),sum(u.passF610)) from Safetyspot u where 1 = 1";
			
		if(StringUtils.isNotEmpty(socialLevel)){
			hql += " and u.spotReport.projectInfo.socialLevel not in (" + socialLevel + ")";
		}
		
		if(StringUtils.isNotEmpty(techGrade)){
			hql += " and u.spotReport.projectInfo.techGrade not in (" + techGrade + ")";
		}
		if(StringUtils.isNotEmpty(startTime)){
			hql += " and u.reportdate >= to_date('"+startTime+"','yyyy-MM-dd')";
		}
		if(StringUtils.isNotEmpty(endTime)){
			hql += " and u.reportdate < to_date('"+endTime+"','yyyy-MM-dd')";
		}
		
		//hql +=  " group by u.projectId ";
		
		return this.find(hql);
	}

	public List<Safetyspot> selectJidu(String startTime, String endTime, String planTypes, String townItems, String roadItems, String levelItems) {
		String hql = "select new com.pengtu.entity.detect.Safetyspot(" +
				"sum(u.countF601),sum(u.passF601),sum(u.countF602),sum(u.passF602)," +
				"sum(u.countF603),sum(u.passF603),sum(u.countF604),sum(u.passF604)," +
				"sum(u.countF605),sum(u.passF605),sum(u.countF606),sum(u.passF606)," +
				"sum(u.countF607),sum(u.passF607),sum(u.countF608),sum(u.passF608)," +
				"sum(u.countF609),sum(u.passF609),sum(u.countF610),sum(u.passF610)) from Safetyspot u where 1 = 1";
			
		if(StringUtils.isNotEmpty(startTime)){
			hql += " and u.reportdate >= to_date('"+startTime+"','yyyy-MM-dd')";
		}
		if(StringUtils.isNotEmpty(endTime)){
			hql += " and u.reportdate < to_date('"+endTime+"','yyyy-MM-dd')";
		}
		
		if(StringUtils.isNotEmpty(townItems)){
			hql += "and (";
			String[] town = townItems.split(",");
			for (int i = 0; i < town.length; i++) {
				if(i == 0){
					hql += " u.spotReport.projectInfo.atTown like '%" + town[i] + "%'";
				}else{
					hql += " or u.spotReport.projectInfo.atTown like '%" + town[i] + "%'";
				}
			}
			hql += ")";
		}
		if(StringUtils.isNotEmpty(planTypes)){
			hql += " and u.spotReport.projectInfo.planType in (" + planTypes + ")";
		}
		
		if(StringUtils.isNotEmpty(roadItems)){
			hql += " and u.spotReport.projectInfo.techGrade in (" + roadItems + ")";
		}

		if(StringUtils.isNotEmpty(levelItems)){
			hql += " and u.spotReport.projectInfo.socialLevel in ('" + levelItems + "')";
		}
		
		return this.find(hql);
	}

	public List<Safetyspot> selectJiduProj(String startTime, String endTime,
			String planTypes, String townItems, String roadItems,
			String levelItems) {
		String hql = "select new com.pengtu.entity.detect.Safetyspot(" +
				"sum(u.countF601),sum(u.passF601),sum(u.countF602),sum(u.passF602)," +
				"sum(u.countF603),sum(u.passF603),sum(u.countF604),sum(u.passF604)," +
				"sum(u.countF605),sum(u.passF605),sum(u.countF606),sum(u.passF606)," +
				"sum(u.countF607),sum(u.passF607),sum(u.countF608),sum(u.passF608)," +
				"sum(u.countF609),sum(u.passF609),sum(u.countF610),sum(u.passF610),u.projectId) from Safetyspot u where 1 = 1";
			
		if(StringUtils.isNotEmpty(startTime)){
			hql += " and u.reportdate >= to_date('"+startTime+"','yyyy-MM-dd')";
		}
		if(StringUtils.isNotEmpty(endTime)){
			hql += " and u.reportdate < to_date('"+endTime+"','yyyy-MM-dd')";
		}
		
		if(StringUtils.isNotEmpty(townItems)){
			hql += "and (";
			String[] town = townItems.split(",");
			for (int i = 0; i < town.length; i++) {
				if(i == 0){
					hql += " u.spotReport.projectInfo.atTown like '%" + town[i] + "%'";
				}else{
					hql += " or u.spotReport.projectInfo.atTown like '%" + town[i] + "%'";
				}
			}
			hql += ")";
		}
		if(StringUtils.isNotEmpty(planTypes)){
			hql += " and u.spotReport.projectInfo.planType in (" + planTypes + ")";
		}
		
		if(StringUtils.isNotEmpty(roadItems)){
			hql += " and u.spotReport.projectInfo.techGrade in (" + roadItems + ")";
		}

		if(StringUtils.isNotEmpty(levelItems)){
			hql += " and u.spotReport.projectInfo.socialLevel in ('" + levelItems + "')";
		}
		hql +=  " group by u.projectId ";
		return this.find(hql);
	}

	public List<Safetyspot>  selectViewPro(String startTime, String endTime,
			String biaoduan, String projectId) {
		String hql = "select new com.pengtu.entity.detect.Safetyspot(" +
				"sum(u.countF601),sum(u.passF601),sum(u.countF602),sum(u.passF602)," +
				"sum(u.countF603),sum(u.passF603),sum(u.countF604),sum(u.passF604)," +
				"sum(u.countF605),sum(u.passF605),sum(u.countF606),sum(u.passF606)," +
				"sum(u.countF607),sum(u.passF607),sum(u.countF608),sum(u.passF608)," +
				"sum(u.countF609),sum(u.passF609),sum(u.countF610),sum(u.passF610)) from Safetyspot u where 1 = 1";
		if(StringUtils.isNotEmpty(startTime)){
			hql += " and u.reportdate >= to_date('"+startTime+"','yyyy-MM-dd')";
		}
		if(StringUtils.isNotEmpty(endTime)){
			hql += " and u.reportdate < to_date('"+endTime+"','yyyy-MM-dd')";
		}
		if(StringUtils.isNotEmpty(biaoduan)){
			hql += " and u.spotReport.signid = '" + biaoduan + "'";
		}
		
		if(StringUtils.isNotEmpty(projectId)){
			hql += " and u.projectId = '" + projectId +"'";
		}
		return this.find(hql);
	}

	public List<Safetyspot> selectByGroupby(String startTime, String endTime,
			String planTypes, String townItems, String roadItems,
			String levelItems, String groupBy) {
		String hql = "select new com.pengtu.entity.detect.Safetyspot(" +
				"sum(u.countF601),sum(u.passF601),sum(u.countF602),sum(u.passF602)," +
				"sum(u.countF603),sum(u.passF603),sum(u.countF604),sum(u.passF604)," +
				"sum(u.countF605),sum(u.passF605),sum(u.countF606),sum(u.passF606)," +
				"sum(u.countF607),sum(u.passF607),sum(u.countF608),sum(u.passF608)," +
				"sum(u.countF609),sum(u.passF609),sum(u.countF610),sum(u.passF610),"+groupBy+") from Safetyspot u where 1 = 1";
		if(StringUtils.isNotEmpty(startTime)){
			hql += " and u.reportdate >= to_date('"+startTime+"','yyyy-MM-dd')";
		}
		if(StringUtils.isNotEmpty(endTime)){
			hql += " and u.reportdate < to_date('"+endTime+"','yyyy-MM-dd')";
		}
		if(StringUtils.isNotEmpty(townItems)){
			hql += " and u.spotReport.projectInfo.atTown in ('" + townItems + "')";
		}
		
		if(StringUtils.isNotEmpty(planTypes)){
			hql += " and u.spotReport.projectInfo.planType in (" + planTypes + ")";
		}
		
		if(StringUtils.isNotEmpty(roadItems)){
			hql += " and u.spotReport.projectInfo.techGrade in (" + roadItems + ")";
		}

		if(StringUtils.isNotEmpty(levelItems)){
			hql += " and u.spotReport.projectInfo.socialLevel in ('" + levelItems + "')";
		}
		
		hql +=  " group by " + groupBy;
		return this.find(hql);
	}
}

